package cn.iocoder.yudao.module.member.utils;


import cn.iocoder.yudao.framework.common.util.date.DateUtils;
import cn.iocoder.yudao.framework.common.util.io.FileUtils;
import cn.iocoder.yudao.framework.common.util.json.JsonUtils;
import cn.iocoder.yudao.module.member.controller.admin.student.vo.StudentExcel;
import cn.iocoder.yudao.module.member.controller.admin.studentCurriculumVitae.vo.StudentCurriculumVitaeExcelVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.*;


@Slf4j
public class ExcelUtil {
    private static  List<String> stuKeyHeadList =Arrays.asList("studentName","loginDate","statusText","mobile","cardNo",
            "createTime","sex","birthday","email" );
    private static   List<String> vitKeyHeadList = Arrays.asList("name","studentPictureUrl","sex",
            "political","liveCity","nativePlace","clan","phoneNumber","email","internshipDesire","educationalJson","specialty","courses","workExperience",
            "projectExperience","languageAbility","specialtyAbility","certificates","practiceExperience");
    private static   List<String> head=Arrays.asList("学生姓名","最后登录时间", "学生认证状态","手机号码","身份证" ,"创建时间","性别","出生日期","邮箱"
            ,"简历名称","学生大头照","性别","政治面貌","居住城市"
            ,"籍贯","民族","手机号码","邮箱"
            ,"求职意向","教育经历","专业","已修课程","工作/实习经历"
            ,"项目经历","语言能力","专业技能","证书","社会实践/活动经历");
    public static void main(String[] args) {
        try {
            String s="/work/dev/yudao-server/down/ddd.xls";
            System.out.println(s.substring(s.lastIndexOf("/")+1,s.length()) );
//            List<StudentExcel> list= JsonUtils.parseArray(s ,StudentExcel.class);
//
//           ExcelUtil.exportExcel(list,   null,1,null);
//            String path2 = ExcelUtil.getInstance().exportExcel2(mapArrayList, head, "E:\\RUN_SYSTEM\\", "excel导出1");
//            System.out.println(path2);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     *
     * @param list
     * @param excelPath
     * @param type 1:直接导出 2:异步导出
     */
    public static String exportExcel (List<StudentExcel> list,String excelPath,Integer type,HttpServletResponse response) {
        String url="";
//        System.out.println(JsonUtils.toJsonString(list));
        HSSFWorkbook wb;
        // 定义一个新的工作簿
        wb = new HSSFWorkbook();

        // 创建一个Sheet页
        HSSFSheet sheet = wb.createSheet();
        sheet.createFreezePane( 0, 1, 0, 1 );
        // 设置行高
        sheet.setDefaultRowHeight((short) (2 * 256));
        // 设置列宽
        for (int i = 0; i < head.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置单元格字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
        // 创建单元格文字居中样式并设置标题单元格居中
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 表头设置冻结与北京颜色
        cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 表头
        HSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle2);
        }

        if (CollectionUtils.isNotEmpty(list)) {
            // 写入数据
            int num=1;
            for (int j = 0; j < list.size(); j++) {
                //学生数据
                StudentExcel student = list.get(j);
                //简历
                List<StudentCurriculumVitaeExcelVO> vList =student.getChildList();
                int size =0;
                if(CollectionUtils.isNotEmpty(vList)){
                    size= vList.size();
                    int stuKeyHeadSize = stuKeyHeadList.size();
                    int vitKeyHeadSize = vitKeyHeadList.size();
                    for (int i = 0; i < size; i++) {
                        HSSFRow row = sheet.createRow(num+i );
                        for (int k = 0; k <stuKeyHeadSize; k++) {
                            HSSFCell sCell = row.createCell(k);
                            sCell.setCellValue(getStuFieldValue(student,stuKeyHeadList.get(k)));
                            sCell.setCellStyle(cellStyle);
                        }
                        for (int f= 0; f< vitKeyHeadSize; f++) {
                            HSSFCell fCell = row.createCell(f+stuKeyHeadSize);
                            fCell.setCellValue(getVitaeFieldValue(vList.get(i),vitKeyHeadList.get(f)));
                            fCell.setCellStyle(cellStyle);
                        }
                        if(size>1 && i==size-1){
                            for (int k = 0; k < stuKeyHeadList.size(); k++) {
                                CellRangeAddress cellRangeAddress = new CellRangeAddress(num, num + size - 1,k,k);
                                sheet.addMergedRegion(cellRangeAddress);
                            }
                        }
                    }
                    num=num+size;
                }else{
                    HSSFRow row = sheet.createRow(num);
                    int stuKeyHeadSize = stuKeyHeadList.size();
                    for (int k = 0; k <stuKeyHeadSize; k++) {
                        HSSFCell sCell = row.createCell(k);
                        sCell.setCellValue(getStuFieldValue(student,stuKeyHeadList.get(k)));
                        sCell.setCellStyle(cellStyle);
                    }
                    num=num+1;
                }
            }
        }

        if(1==type){
            out(wb,response);
        }else{
           url= getPath(wb,excelPath);
        }
        return url;
    }

    public static String getPath(HSSFWorkbook wb,String excelPath){
        FileOutputStream out=null;
        try {
            String date=DateUtils.format(new Date(),"yyyyMMdd");
            String filename="学生信息_"+DateUtils.format(new Date(),"yyyyMMddHHmmss");;
            excelPath=excelPath+"/"+date;
            FileUtils.mkdir(excelPath);
            String path = excelPath +"/"+ filename + ".xls";
            log.info("path----"+path);
            File file = new File(path);
            out = new FileOutputStream(file);
            wb.write(out);
            return path;
        } catch (IOException e) {
            log.error("输出流异常",e);
        }finally {
            if(null!=wb){
                try {
                    wb.close();
                } catch (IOException e) {
                    log.error("输出流异常",e);
                }
            }
            if(null!=out){
                try {
                    out.flush();
                    out.close();
                }catch (Exception e){
                    log.error("输出流异常",e);
                }
            }
        }
        return "";
    }

    public static void out(HSSFWorkbook wb ,HttpServletResponse resp){
        OutputStream output=null;
        try {
            //输出Excel文件
            output = resp.getOutputStream();
            // 设置附件响应头，浏览器会进行下载
            resp.setHeader("Content-disposition", "attachment; filename=学生信息.xls");
            //响应正文的MIME类型，表示Excel
            resp.setContentType("application/vnd.ms-excel");
            wb.write(output);
        } catch (IOException e) {
           log.error("输出流异常",e);
        }finally {
            if(null!=wb){
                try {
                    wb.close();
                } catch (IOException e) {
                    log.error("输出流异常",e);
                }
            }
            if(null!=output){
                try {
                    output.flush();
                    output.close();
                } catch (IOException e) {
                    log.error("输出流异常",e);
                }
            }
        }
    }

    public String exportExcel2 (List<LinkedHashMap<String, Object>> list, List<String> head,
                                String excelPath, String filename) throws Exception {

        Map<String, Object> map = list.get(0);
        Set<String> stringSet = map.keySet();
        ArrayList<String> field = new ArrayList<>(stringSet);

        // 定义一个新的工作簿
        XSSFWorkbook wb = new XSSFWorkbook();

        // 创建一个Sheet页
        XSSFSheet sheet = wb.createSheet();
        sheet.createFreezePane( 0, 1, 0, 1 );
        // 设置行高
        sheet.setDefaultRowHeight((short) (2 * 256));
        // 设置列宽
        for (int i = 0; i < head.size(); i++) {
            sheet.setColumnWidth(i, 4000);
        }
        // 设置单元格字体样式
        XSSFFont font = wb.createFont();
        font.setFontName("等线");
        font.setFontHeightInPoints((short) 16);
        // 创建单元格文字居中样式并设置标题单元格居中
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        XSSFCellStyle cellStyle2 = wb.createCellStyle();
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        // 表头设置冻结与北京颜色
        cellStyle2.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 表头
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < head.size(); i++) {
            XSSFCell cell = row1.createCell(i);
            cell.setCellValue(head.get(i));
            cell.setCellStyle(cellStyle2);
        }
        // 写入数据
        for (int j = 0; j < list.size(); j++) {
            XSSFRow row = sheet.createRow(j + 1);
            for (int i = 0; i < field.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellValue(list.get(j).get(field.get(i)).toString());
                cell.setCellStyle(cellStyle);
            }
        }


        String path = excelPath + filename + ".xlsx";
        File savefile = new File(excelPath);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        File file = new File(path);
        FileOutputStream fileOutputStream = new FileOutputStream(file);
        wb.write(fileOutputStream);
        wb.close();
        fileOutputStream.close();
        return path;

    }

    public static String getStuFieldValue(StudentExcel stu,String fieldName){
        String sVal="";
        try {
            // 获取字段名为name的字段对象
            Field nameField =stu.getClass().getDeclaredField(fieldName);
            // 设置字段可访问
            nameField.setAccessible(true);
            // 获取字段值
            Object obj = nameField.get(stu);
            String simpleName = nameField.getType().getSimpleName();
            if("Date".equals(simpleName)){
                sVal=DateUtils.format((Date) obj,DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND);
            }else {
                sVal=toString(obj);
            }
        } catch (Exception e) {
            log.error("获取学生字段值报错，StudentId:"+stu.getId(),e);
        }
        return sVal;
    }


    public  static String getVitaeFieldValue(StudentCurriculumVitaeExcelVO vitae, String fieldName){
        String sVal="";
        try {
            // 获取字段名为name的字段对象
            Field nameField =vitae.getClass().getDeclaredField(fieldName);
            // 设置字段可访问
            nameField.setAccessible(true);
            // 获取字段值
            Object obj = nameField.get(vitae);
            String simpleName = nameField.getType().getSimpleName();
            if("Date".equals(simpleName)){
                sVal=DateUtils.format((Date) obj,DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND);
            }else {
                sVal=toString(obj);
            }
        } catch (Exception e) {
            log.error("获取学生简历信息字段值报错，id:"+vitae.getId(),e);
        }
        return sVal;
    }


    public static String toString(Object obj){
        if(null==obj || StringUtils.isBlank(obj.toString())){
            return "";
        }
        return obj.toString();
    }


}

